package com.byxm.wlgw.query;

import java.util.HashMap;
import java.util.Iterator;

import com.byxm.wlgw.common.ComConst;
import com.byxm.wlgw.common.ComUtil;

/**
 * @function: BaseQuery，包含生成查询条件的SQL的方法
 * @modify history
 * Date           Author            Description
 * --------------------------------------------------------
 * 2010-2-5      fankb        Created
 *
 */
public class BaseQuery {
	private String currentDialect=ComConst.DATABASE_TYPE;//数据库类型
	private String sTableNm;//表名
	/**
	 * 构造函数
	 * @param currentDialect
	 */
	public BaseQuery(String sTableNm) {
		this.sTableNm = sTableNm;
	}
	
	/**
	 * 查找所有(分页)
	 * @param sTableNm表名
	 * @param orderBy排序字段
	 */
	public String findAll(String orderBy,int first,int size) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(sTableNm);
		if(!ComUtil.isNull(orderBy)) {
			sql.append(" order by ").append(orderBy);
		}
		return getSqlLimit(sql.toString(),first,size);
	}
	/**
	 * 查找所有
	 * @param sTableNm表名
	 */
	public String findAll(String orderBy) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(sTableNm);
		if(!ComUtil.isNull(orderBy)) {
			sql.append(" order by ").append(orderBy);
		}
		return sql.toString();
	}
	
	/**
	 * 根据字段查找
	 * @param sTableNm 表名
	 * @param field字段名
	 * @param unique 是否返回一条
	 */
	public String findByField(String orderBy,Integer size,String... field) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(sTableNm).append(" where ");
		if(field!=null&&field.length>0) {
			for(int i =0;i<field.length;i++) {
				if(i==0) {
					sql.append(field[i]).append("=? ");
				} else {
					sql.append("and ").append(field[i]).append("=? ");
				}
			}
		}
		if(!ComUtil.isNull(orderBy)){
			sql.append(" order by "+orderBy);
		}
		if(size != null) {
			return getSqlLimit(sql.toString(),0,size);
		}
		return sql.toString();
	}
	
	
	/**
	 * 根据字段查找返回指定字段
	 * @param sTableNm 表名
	 * @param rtnField 返回字段
	 * @param field字段名
	 * @param unique 是否返回一条
	 */
	public String findNAByField(String orderBy,boolean unique,String rtnField,String... field) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(sTableNm).append(" where ");
		if(field!=null&&field.length>0) {
			for(int i =0;i<field.length;i++) {
				if(i==0) {
					sql.append(field[i]).append("=? ");
				} else {
					sql.append("and ").append(field[i]).append("=? ");
				}
			}
		}
		if(!ComUtil.isNull(orderBy)){
			sql.append(" order by "+orderBy);
		}
		if(unique) {
			return getSqlLimit(sql.toString(),0,1);
		}
		return sql.toString();
	}	
	
	/**
	 * 根据字段查找返回指定字段(锁表)
	 * @param sTableNm 表名
	 * @param rtnField 返回字段
	 * @param field字段名
	 * @param unique 是否返回一条
	 */
	public String findNAByFieldLocked(String orderBy,boolean unique,String rtnField,String... field) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(sTableNm).append(" where ");
		if(field!=null&&field.length>0) {
			for(int i =0;i<field.length;i++) {
				if(i==0) {
					sql.append(field[i]).append("=? ");
				} else {
					sql.append("and ").append(field[i]).append("=? ");
				}
			}
		}
		if(!ComUtil.isNull(orderBy)){
			sql.append(" order by "+orderBy);
		}
		if(unique) {
			return getSqlLimit(sql.toString(),0,1);
		}
		sql.append(" for update");
		return sql.toString();
	}	
	
	/**
	 * 根据字段查找(锁表)
	 * @param sTableNm 表名
	 * @param field字段名
	 * @param unique 是否返回一条
	 */
	public String findByFieldLocked(String orderBy,boolean unique,String... field) {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(sTableNm).append(" where ");
		if(field!=null&&field.length>0) {
			for(int i =0;i<field.length;i++) {
				if(i==0) {
					sql.append(field[i]).append("=? ");
				} else {
					sql.append("and ").append(field[i]).append("=? ");
				}
			}
		}
		if(!ComUtil.isNull(orderBy)){
			sql.append(" order by "+orderBy);
		}
		if(unique) {
			return getSqlLimit(sql.toString(),0,1);
		}
		sql.append(" for update");
		return sql.toString();
	}	
	/**
	 * 查询某表的所有记录数
	 * @return
	 */
	public String findToal(){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(rowid) from ").append(sTableNm);
		return sql.toString();
	}
	/**
	 * 查询某条sql语句返回的条数
	 */
	public String findToal(String strSql){
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from (").append(strSql).append(")");
		return sql.toString();
	}

	/**
	 * 更新某条记录的字段
	 * @param field1 条件字段
	 * @param field2 更新字段
	 * @return
	 */
	public String updateField(String field1,String... field2){
		StringBuffer sql = new StringBuffer();
		sql.append("update ").append(sTableNm).append(" set ");
		if(field2!=null&&field2.length>0) {
			for(int i =0;i<field2.length;i++) {
				if(i==0) {
					sql.append(field2[i]).append("=?");
				} else {
					sql.append(",").append(field2[i]).append("=? ");
				}
			}
		}
		sql.append(" where ").append(field1).append("=?");
		return sql.toString();
	}
	
	/**
	 * 根据字段删除记录
	 * @param field
	 * @return
	 */
	public String deleteByField(String... field){
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(sTableNm).append(" where ");
		if(field!=null&&field.length>0) {
			for(int i =0;i<field.length;i++) {
				if(i==0) {
					sql.append(field[i]).append("=?");
				} else {
					sql.append(" and ").append(field[i]).append("=?");
				}
			}
		}
		return sql.toString();
	}
	/**
	 * 删除所有记录
	 * @return
	 */
	public String deleteAll(){
		StringBuffer sql = new StringBuffer();
		sql.append("delete from ").append(sTableNm);
		return sql.toString();
	}
	
/**************************************************私有方法*************************************************************************/
	/**
	 * 获取时间比较的sql语句
	 */
	protected String getDateCompare(String value) {
		String dataStr = null;
		//#############Oracle############### 
		 if(currentDialect.toLowerCase().indexOf("oracle") >= 0) { 
			 dataStr = new StringBuffer("to_date('").append(value).append("','yyyy-MM-dd')").toString();
		 }
		 //############## SQLServer ################
		 else if(currentDialect.toLowerCase().indexOf("sqlserver") >= 0) {
			 dataStr = new StringBuffer("cast(").append(value).append("as datetime").toString();
		 }
		 //#############  缺省使用的是mysql   ############## 
		 else {
			 dataStr = new StringBuffer("str_to_date(").append(value).append("GET_FORMAT(DATE,'USA'))").toString();
		 }
		 return dataStr;
	}
	
	/**  
     * 获取分页的SQL语句  
     * polarbear 2009-5-9   
     * @param _sql  
     * @return  
     */  
    public String getSqlLimit(String sql, int start, int limit) {   
           
    	//#############Oracle分页方式###############  
        if(currentDialect.toLowerCase().indexOf("oracle") >= 0) {   
            sql = sql.trim();   
            boolean isForUpdate = false;   
            if ( sql.toLowerCase().endsWith(" for update") ) {   
                sql = sql.substring( 0, sql.length()-11 );   
                isForUpdate = true;   
            }   
               
            StringBuffer pagingSelect = new StringBuffer(sql.length()+100 );     
            pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");     
            pagingSelect.append(sql);    
            pagingSelect.append(" ) row_ where rownum <= " + (start + limit) + ") where rownum_ > " + start);     
            if (isForUpdate) pagingSelect.append(" for update");   
            return pagingSelect.toString();   
        }         
        //############## SQLServer分页方式 ################    
        else if(currentDialect.toLowerCase().indexOf("sqlserver") >= 0) {   
            StringBuffer pagingBuilder = new StringBuffer();     
            String orderby = getOrderByPart(sql);     
            String distinctStr = "";     
  
            String loweredString = sql.toLowerCase();     
            String sqlPartString = sql.trim();     
            if (loweredString.trim().startsWith("select")) {     
                int index = 6;     
                if (loweredString.startsWith("select distinct")) {     
                    distinctStr = "DISTINCT ";     
                    index = 15;     
                }     
                sqlPartString = sqlPartString.substring(index);     
            }     
            pagingBuilder.append(sqlPartString);     
       
            if (orderby == null || orderby.length() == 0) {     
                orderby = "ORDER BY CURRENT_TIMESTAMP";     
            }     
            StringBuffer result = new StringBuffer();     
            result.append("SELECT * FROM (")      
            .append("SELECT ")     
            .append(distinctStr)     
            .append(" TOP 100 PERCENT ROW_NUMBER() OVER (") //使用TOP 100 PERCENT可以提高性能     
            .append(orderby)     
            .append(") AS __hibernate_row_nr__, ")     
            .append(pagingBuilder)     
            .append(") as ucstarTempTable WHERE __hibernate_row_nr__ >")   
            .append(start)     
            .append(" AND __hibernate_row_nr__ <=")     
            .append(start + limit)     
            .append(" ORDER BY __hibernate_row_nr__");     
            return result.toString();   
        }   
           
        //#############  缺省使用的是mysql的分页方式   ##############   
        else {     
        	 return new StringBuffer(sql.length()+20 )   
             .append(sql)   
             .append(" limit " + start + ", " + limit)   
             .toString();      
        }   
    }  
    
    /**  
     * SQLServer的处理  
     * polarbear 2009-5-9   
     * @param sql  
     * @return  
     */  
    private String getOrderByPart(String sql) {   
        String loweredString = sql.toLowerCase();   
        int orderByIndex = loweredString.indexOf("order by");   
        if (orderByIndex != -1) {    
            return sql.substring(orderByIndex);   
        } else {   
            return "";   
        }   
    }  

}
